<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>Import từ file excel</title>
<link href="/htqlmba/libraries/css/capnhatdv.css" type="text/css" rel="stylesheet">
</head>

<body>

<form action="" method="post" enctype="multipart/form-data" name="form1">
  <table width="715" border="0" align="center" cellspacing="0">
    <tr>
      <td height="23" colspan="5" class="TableHeader1" scope="col">Thêm trạm từ file Excel</td>
    </tr>
    <tr class="TableField">
      <td width="83">&nbsp;</td>
      <td width="322"><input type="file" name="file" id="file" accept=".xls, .xlsx" /></td>
      <td width="208"><input type="submit" name="upload" id="upload" value="Upload từ file excel" /></td>
      <td width="118">&nbsp;</td>
      <td width="16">&nbsp;</td>
    </tr>
  </table>
</form>
<?php 
include_once($_SERVER['DOCUMENT_ROOT'].'/htqlmba/models/m_tram.php');
include_once($_SERVER['DOCUMENT_ROOT'] . '/htqlmba/libraries/PHPExcel_1.7.9_doc/Classes/PHPExcel.php');
        include_once($_SERVER['DOCUMENT_ROOT'] . '/htqlmba/libraries/PHPExcel_1.7.9_doc/Classes/PHPExcel/IOFactory.php');
$sodongthemvao=0;
$sodongloi=0;

if( isset($_POST['upload']) ){
	if( $_FILES["file"]["name"] != "" ){
		


$objPHPExcel = PHPExcel_IOFactory::load($_FILES["file"]["tmp_name"]);
$objWorksheet = $objPHPExcel->setActiveSheetIndex(0);
foreach ( $objWorksheet->getRowIterator() as $row) {
     $worksheetTitle = $objWorksheet->getTitle();
     $highestRow = $objWorksheet->getHighestRow(); // e.g. 10
     $highestColumn = $objWorksheet->getHighestColumn(); // e.g 'F'
     $highestColumnIndex = PHPExcel_Cell::columnIndexFromString($highestColumn);
}

//
$nrColumns = $highestColumnIndex;


// hien thong tin file excel
/*echo "File :".$worksheetTitle." has ";
echo $nrColumns . ' columns';
echo ' y ' . $highestRow . ' rows.';*/
echo " <a class='DataDetail' >";
echo "File :".$worksheetTitle." có  ";
echo ' ' . $highestRow-2 . ' dòng dử liệu trạm.';
echo "<br>";
echo "Data:";
echo "<br>";
echo " </a>";



		
		
		// hien thi ra bang thong bao cac may import
echo ' <table cellpadding="3" cellspacing="1" border="0" align="center" width="900"><tr>';
// header cua bang hien thi
echo "<tr>";
echo "<th  class='TableHeader' width='200'>Kết quả Import</th>";
echo "<th  class='TableHeader' width='150'>Mã trạm</th>";
  echo "<th  class='TableHeader' width='250'>Tên trạm</th>";
 echo "<th class='TableHeader' width='300'>Địa chỉ trạm</th>"; 
echo " </tr>";




// hien thi thong tin bang excel
for ($row = 3; $row <= $highestRow; ++ $row) {

	// bat dau lay du lieu tu file excel hien len
   echo '<tr>';
  // truoc moi dong du lieu them vao ket qua import
   $ketquaip="ketqua";
   		
	$TRAM_TEST = new Tram();
	
	$cell_test= $objWorksheet->getCellByColumnAndRow(0, $row);
	$MA_TRAM_TEST=$cell_test->getValue();
	$TRAM_TEST->setMA_TRAM($MA_TRAM_TEST);
	if ($TRAM_TEST->isExits("tram","MA_TRAM='".$TRAM_TEST->getMA_TRAM()."'") <= 0  ){
            $ketquaip=" OK !";			
        }
        else{
           $ketquaip=" Mã trạm đã tồn tại !";		   
        }	
		
	
	 // kiem tra cac truong bat buoc
	 // ma tram
					$cell_test = $objWorksheet->getCellByColumnAndRow(0, $row);
                    $MA_TRAM_TEST = $cell_test->getValue();
					if ($MA_TRAM_TEST==""){$ketquaip = " Thiếu mã trạm !";}
	// ten tram
					$cell_test = $objWorksheet->getCellByColumnAndRow(1, $row);
                    $TEN_TRAM_TEST = $cell_test->getValue();
					if ($TEN_TRAM_TEST==""){$ketquaip = " Thiếu tên trạm !";}
					
					$db = new database();
                    $db->setQuery("select * from tram");
                    $result = $db->fetchAll();
                    while ($rows = mysql_fetch_array($result)) {
                        if (mb_strtolower($TEN_TRAM_TEST,'UTF-8') == mb_strtolower($rows['TEN_TRAM'],'UTF-8')) {
                            $ketquaip = " Tên trạm đã tồn tại!";
                        }
                    }
							
				
	// dia chi tram
					$cell_test = $objWorksheet->getCellByColumnAndRow(2, $row);
                    $DIA_CHO_TRAM_TEST = $cell_test->getValue();
					if ($DIA_CHO_TRAM_TEST==""){$ketquaip = " Thiếu địa chỉ trạm !";}
	
	
   
	

   		if($ketquaip==" OK !"){
			$sodongthemvao++;
  
  									if($row%2 ==0 ){  
          					 						echo '<td align="center" class="TableField2" > ' . $ketquaip . '</td>';
	  					 							} 
	   										else 
	  				 								{ 
	  						 						echo '<td align="center" class="TableField" > ' . $ketquaip . '</td>';
	   												}
		// co gan thuc hien them ngay tai day
											 // $sql="insert into tablename(column1, column2, column3, column4, column5, column6)
  // values('".$val[1] . "','" . $val[2] . "','" . $val[3]. "','" . $val[4]. "','" . $val[5]. "','" . $val[6]. "')";
//Run your mysql_query
	$Tram = new Tram();
	
    // thong tin chinh - bang may_bien_ap
		$cell_test = $objWorksheet->getCellByColumnAndRow(0, $row);
            $MA_TRAM = $cell_test->getValue();
		  
		 $cell_test =$objWorksheet->getCellByColumnAndRow(1, $row);
		 $TEN_TRAM = $cell_test->getValue();
		  $cell_test = $objWorksheet->getCellByColumnAndRow(2, $row);
		 $DIA_CHI_TRAM = $cell_test->getValue();
	
	// gan cac thong tin tu 1 dong cua file excel vao 1 doi tuong -> them vao csdl
    $Tram->setMA_TRAM($MA_TRAM);
    $Tram->setTEN_TRAM($TEN_TRAM);
	$Tram->setDIA_CHI_TRAM($DIA_CHI_TRAM);	
	$ketquaip2=" OK !";
	
	$cell_test= $objWorksheet->getCellByColumnAndRow(0, $row);
	$MA_TRAM_TEST=$cell_test->getValue();
	$TRAM_TEST->setMA_TRAM($MA_TRAM_TEST);
	if ($TRAM_TEST->isExits("tram","MA_TRAM='".$TRAM_TEST->getMA_TRAM()."'") <= 0  ){
            $ketquaip2=" OK !";
		
        }
        else{
           $ketquaip2=" Mã trạm đã tồn tại !";
		
        }
	  // kiem tra cac truong bat buoc
	 // ma tram
					$cell_test = $objWorksheet->getCellByColumnAndRow(0, $row);
                    $MA_TRAM_TEST = $cell_test->getValue();
					if ($MA_TRAM_TEST==""){$ketquaip2 = " Thiếu mã trạm !";}
	// ten tram
					$cell_test = $objWorksheet->getCellByColumnAndRow(1, $row);
                    $TEN_TRAM_TEST = $cell_test->getValue();
					if ($TEN_TRAM_TEST==""){$ketquaip2 = " Thiếu tên trạm !";}
					
					$db = new database();
                    $db->setQuery("select * from tram");
                    $result = $db->fetchAll();
                    while ($rows = mysql_fetch_array($result)) {
                        if (mb_strtolower($TEN_TRAM_TEST,'UTF-8') == mb_strtolower($rows['TEN_TRAM'],'UTF-8')) {
                            $ketquaip2 = " Tên trạm đã tồn tại!";
                        }
                    }
					
	// dia chi tram
					$cell_test = $objWorksheet->getCellByColumnAndRow(2, $row);
                    $DIA_CHO_TRAM_TEST = $cell_test->getValue();
					if ($DIA_CHO_TRAM_TEST==""){$ketquaip2 = " Thiếu địa chỉ trạm !";}
					$TRAM_TEST = new Tram();
	

   		if($ketquaip2==" OK !"){
	 
    if ($Tram->isExits("Tram","MA_TRAM='".$Tram->getMA_TRAM()."'") <= 0  ){
            if ($Tram->themTram() > 0){
        			//echo " OK! ";
            }
            else{
                //	echo "Khong thanh cong";
            } 
        }
        else{
            //MBA da ton tai
            //	echo "Bi trung so N0";
        }
		}
											
											
													
		} 
		else { 
		$sodongloi++;
		if($row%2 ==0 ){  
          					 echo '<td align="center" class="TableField2error" > ' . $ketquaip . '</td>';
	  					 	} 
	   							else 
	  				 			{ 
	  						 echo '<td align="center" class="TableFielderror" > ' . $ketquaip . '</td>';
	   							}  
						}
	   
	   
	   
	   // in du lieu tu file excel
   for ($col = 0; $col < $nrColumns; ++ $col) {
	   $cell = $objWorksheet->getCellByColumnAndRow($col, $row);
       $val = $cell->getValue();

		
	   if($row%2 ==0 )
	   {
         	 echo '<td align="center" class="TableField2" > ' . $val . '</td>';
	   	} 
	   else { 
	   			echo '<td align="center" class="TableField" > ' . $val . '</td>';}
   			}
echo '</tr>';

}
echo '</table>';





// quang vao csdl
for ($row = 3; $row <= $highestRow; ++ $row) {
	$val=array();
for ($col = 0; $col < $nrColumns;  $col++) {
   $cell = $objWorksheet->getCellByColumnAndRow($col, $row);
   $val[] = $cell->getValue();
 }
 
 
		
		
}

	 echo "<a class='DataDetail'>Số dòng đã thêm vào hệ thống: ";
     echo $sodongthemvao;
	 echo "</a>";
	 echo "<br>";
	 echo "<a class='error'>Số dòng lỗi: ";
	 echo $sodongloi;
	 echo "<br>";


	} else { echo "Chọn file excel để upload!"; }
 	 
	 
	 
	 
}
?>




</body>
</html>



